using System;
using System.Collections;
using System.Data;
using System.Text;
using TimeSheetDAL;

namespace TimeSheetBLL
{
    /// <summary>
    /// Summary description for DBFacade.
    /// </summary>
    public class DBFacade
    {
        private DataAccess data;

        public DBFacade()
        {
            data = new DataAccess();
        }

        #region User

        /// <summary>
        /// Authenticates the given User based upon the objects'
        /// username and password properties
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool Authenticate(User user)
        {
            bool valid = false;

            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM users ");
            sb.Append("WHERE email = '").Append(BrokerTools.PrepForDB(user.Email)).Append(
                "' ");
            sb.Append("AND password = '").Append(BrokerTools.PrepForDB(user.Password)).
                Append("' ");

            DataTable dt = data.GetTable(sb.ToString());
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    user.Active = Convert.ToBoolean(dt.Rows[0]["active"]);
                    user.DateCreated = (DateTime) dt.Rows[0]["date_created"];
                    user.Email = (string) dt.Rows[0]["email"];
                    user.FirstName = (string) dt.Rows[0]["first_name"];
                    user.LastName = (string) dt.Rows[0]["last_name"];
                    user.Password = (string) dt.Rows[0]["password"];
                    user.UserName = (string) dt.Rows[0]["user_name"];
                    user.ID = Convert.ToInt32(dt.Rows[0]["user_id"]);
                    valid = true;
                }
            }
            return valid;
        }

        /// <summary>
        /// Gets a table of all of the projects that the given staff member has worked on
        /// in the past 60 days.  The number of days should be pushed up higher in the
        /// API
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable GetRecentProjects(long userID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT DISTINCT project.project_id, project.project_name, client.client_name ");
            sb.Append("FROM chargeablehours, client, project, projectstatus ");
            sb.Append("WHERE chargeablehours.project_id = project.project_id ");
            sb.Append("AND projectstatus.status_id = project.status_id ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND projectstatus.is_visible = 1 ");
            sb.Append("AND chargeablehours.user_id = ").Append(userID).Append(" ");
            sb.Append("AND charge_date > '").Append(
                DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd")).Append("' ");
            sb.Append("GROUP BY chargeablehours.project_id ");
            sb.Append("ORDER BY client.client_name, project.project_name ");

            return data.GetTable(sb.ToString());
        }

        /// <summary>
        /// This method returns a UserProjects object for the
        /// pay period during which the given DateTime object falls into.  The userID
        /// is the id of the user for whom a list of projects they worked on is required
        /// </summary>
        /// <param name="date">Any date within the desired pay period</param>
        /// <param name="userID">The unique id of the user</param>
        /// <returns>An arraylist of ProjectHours objects</returns>
        public UserProjects GetProjectsByPayPeriod(DateTime date, long userID)
        {
            UserProjects projects = new UserProjects(userID);

            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT project.project_id, project.project_name, client.client_name, charge_date, charge_hours, status_id ");
            sb.Append("FROM project, client, chargeablehours ");
            sb.Append("WHERE project.project_id = chargeablehours.project_id ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND chargeable_hours.user_id = ").Append(userID);
            sb.Append("ORDER BY chargeablehours.project_id");

            DataTable dt = data.GetTable(sb.ToString());
            foreach (DataRow row in dt.Rows)
            {
                projects.AddProjectHours(GetProjectHoursFromRow(row));
            }

            return projects;
        }

        private ProjectHours GetProjectHoursFromRow(DataRow row)
        {
            ProjectHours ph = new ProjectHours();
            ph.ClientName = (string) row["client_name"];
            ph.AddHours(
                Convert.ToDateTime(row["charge_date"]),
                Convert.ToDouble(row["charge_hours"]));
            ph.ProjectID = Convert.ToInt64(row["project_id"]);
            ph.ProjectName = (string) row["project_name"];
            return ph;
        }

        public DataTable GetUserNonChargeableHoursByPayPeriod(DateTime date, long userID)
        {
            string statement = "SELECT " +
                               "nch_id, nonchargeablehours.activity_id, non_charge_date, non_charge_hours, activity.activity_name " +
                               "FROM nonchargeablehours, activity " +
                               "WHERE nonchargeablehours.activity_id = activity.activity_id " +
                               "AND non_charge_date = '" + date.ToString("yyyy-MM-dd") +
                               "' " +
                               "AND user_id = " + userID;

            return data.GetTable(statement);
        }

        public DataTable GetUserChargeableHoursByPayPeriod(DateTime date, long userID)
        {
            string statement = "SELECT " +
                               "ch_id, chargeablehours.project_id, charge_date, charge_hours, project.project_name, client_name " +
                               "FROM chargeablehours, project, client " +
                               "WHERE chargeablehours.project_id = project.project_id " +
                               "AND project.client_id = client.client_id " +
                               "AND charge_date = '" + date.ToString("yyyy-MM-dd") + "' " +
                               "AND user_id = " + userID;

            return data.GetTable(statement);
        }

        /// <summary>
        /// Given a date and the unique id of a user, this method returns a 
        /// Hashtable full of DailyHours objects which tracks the number
        /// of non-chargeable and chargeable hours logged by the user
        /// on each day in the pay period.  The hashtable uses a DateTime object
        /// as the key for the hashtable.
        /// </summary>
        /// <param name="date"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public Hashtable GetUserHoursByDateRange(
            DateTime start, DateTime end, long userID)
        {
            Hashtable ht = new Hashtable();

            DataTable ch = GetChargeableHoursByPayPeriod(start, end, userID);
            foreach (DataRow row in ch.Rows)
            {
                DateTime thisDate = (DateTime) row["charge_date"];
                DailyHours dh = ht[thisDate] as DailyHours;
                if (dh == null)
                {
                    dh = new DailyHours(thisDate);
                    ht.Add(dh.Date, dh);
                }
                dh.ChargeableHours.Add(
                    new ChargeableHours(
                        row["project_name"].ToString(),
                        row["client_name"].ToString(),
                        Convert.ToDouble(row["charge_hours"])));
            }

            DataTable nch = GetNonChargeableHoursByPayPeriod(start, end, userID);
            foreach (DataRow row in nch.Rows)
            {
                DateTime thisDate = (DateTime) row["non_charge_date"];
                DailyHours dh = ht[thisDate] as DailyHours;
                if (dh == null)
                {
                    dh = new DailyHours(thisDate);
                    ht.Add(dh.Date, dh);
                }
                dh.NonChargeableHours.Add(
                    new NonChargeableHours(
                        row["activity_name"].ToString(),
                        Convert.ToDouble(row["non_charge_hours"])));
            }

            return ht;
        }

        private DataTable GetNonChargeableHoursByPayPeriod(
            DateTime start, DateTime end, long userID)
        {
            string statement =
                "SELECT non_charge_hours, non_charge_date, activity_name " +
                "FROM nonchargeablehours, activity " +
                "WHERE user_id = " + userID.ToString() + " " +
                "AND non_charge_date >= '" + start.ToString("yyyy-MM-dd") + "' " +
                "AND non_charge_date <= '" + end.ToString("yyyy-MM-dd") + "' " +
                "AND nonchargeablehours.activity_id = activity.activity_id";

            return data.GetTable(statement);
        }

        private DataTable GetChargeableHoursByPayPeriod(
            DateTime start, DateTime end, long userID)
        {
            string statement =
                "SELECT charge_hours, charge_date, project_name, client_name " +
                "FROM chargeablehours, project, client " +
                "WHERE user_id = " + userID.ToString() + " " +
                "AND charge_date >= '" + start.ToString("yyyy-MM-dd") + "' " +
                "AND charge_date <= '" + end.ToString("yyyy-MM-dd") + "' " +
                "AND project.client_id = client.client_id " +
                "AND chargeablehours.project_id = project.project_id";

            return data.GetTable(statement);
        }

        public double GetTotalChargeableHours(
            long userID, DateTime startDate, DateTime endDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM( charge_hours ) ");
            sb.Append("FROM chargeablehours ");
            sb.Append("WHERE user_id = ").Append(userID).Append(" ");
            sb.Append("AND charge_date >= '").Append(startDate.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("AND charge_date <= '").Append(endDate.ToString("yyyy-MM-dd")).
                Append("' ");

            try
            {
                return Convert.ToDouble(data.GetObject(sb.ToString()));
            }
            catch
            {
                return 0;
            }
        }

        public double GetTotalNonChargeableHours(
            long userID, DateTime startDate, DateTime endDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM( non_charge_hours ) ");
            sb.Append("FROM nonchargeablehours ");
            sb.Append("WHERE user_id = ").Append(userID).Append(" ");
            sb.Append("AND non_charge_date >= '").Append(startDate.ToString("yyyy-MM-dd"))
                .Append("' ");
            sb.Append("AND non_charge_date <= '").Append(endDate.ToString("yyyy-MM-dd")).
                Append("' ");

            try
            {
                return Convert.ToDouble(data.GetObject(sb.ToString()));
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// Given a User ID and a start and end date, this function returns a DataTable with
        /// a list of all the projects the user worked on between the two dates as well as
        /// the total number of hours that were worked on each project.
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetProjectHoursByStaff(
            long userID, DateTime startDate, DateTime endDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM(charge_hours) AS hours, project_name, client_name ");
            sb.Append("FROM chargeablehours, client, project ");
            sb.Append("WHERE chargeablehours.project_id = project.project_id ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND chargeablehours.user_id = ").Append(userID).Append(" ");
            sb.Append("AND charge_date >= '").Append(startDate.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("AND charge_date <= '").Append(endDate.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("GROUP BY project_name, client_name ");
            sb.Append("ORDER BY client_name, project_name ");

            return data.GetTable(sb.ToString());
        }

        public DataTable GetActivityHoursByStaff(
            long userID, DateTime startDate, DateTime endDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM(non_charge_hours) AS hours, activity_name ");
            sb.Append("FROM nonchargeablehours, activity ");
            sb.Append("WHERE nonchargeablehours.activity_id = activity.activity_id ");
            sb.Append("AND nonchargeablehours.user_id = ").Append(userID).Append(" ");
            sb.Append("AND non_charge_date >= '").Append(startDate.ToString("yyyy-MM-dd"))
                .Append("' ");
            sb.Append("AND non_charge_date <= '").Append(endDate.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("GROUP BY activity_name ");
            sb.Append("ORDER BY activity_name ");

            return data.GetTable(sb.ToString());
        }

        #endregion

        #region Administrator

        /// <summary>
        /// Authenticates an Administrator based upon the object's username and password
        /// properties
        /// </summary>
        /// <param name="admin"></param>
        /// <returns></returns>
        public bool Authenticate(Administrator admin)
        {
            bool valid = false;
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM administrator ");
            sb.Append("WHERE admin_name = '").Append(
                BrokerTools.PrepForDB(admin.AdminName)).Append("' ");
            sb.Append("AND password = '").Append(BrokerTools.PrepForDB(admin.Password)).
                Append("' ");

            DataTable dt = data.GetTable(sb.ToString());
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    admin.Active = Convert.ToBoolean(dt.Rows[0]["active"]);
                    admin.AdminName = (string) dt.Rows[0]["admin_name"];
                    admin.Email = (string) dt.Rows[0]["admin_email"];
                    admin.ID = Convert.ToInt32(dt.Rows[0]["admin_id"]);
                    admin.Password = (string) dt.Rows[0]["password"];
                    valid = true;
                }
            }
            return valid;
        }

        #endregion

        #region Client

        /// <summary>
        /// Gets all of the projects for the given client id
        /// </summary>
        /// <param name="clientID"></param>
        /// <returns></returns>
        public DataTable SelectAllProjects(long clientID)
        {
            string statement = "SELECT * FROM project WHERE client_id = " + clientID +
                               " ORDER BY project_name";
            return data.GetTable(statement);
        }

        #endregion

        #region ProjectStatus

        public DataTable GetProjectsByStatusID(long statusID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT project_name, project_id, client_name ");
            sb.Append("FROM client, project, projectstatus ");
            sb.Append("WHERE client.client_id = project.client_id ");
            sb.Append("AND projectstatus.status_id = project.status_id ");
            sb.Append("AND projectstatus.status_id = ").Append(statusID).Append(" ");
            sb.Append("ORDER BY client_name, project_name ");

            return data.GetTable(sb.ToString());
        }

        #endregion

        #region Project

        public DataTable GetAllPublicProjects()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT project.project_id, project_name, client_name ");
            sb.Append("FROM client, project, projectstatus ");
            sb.Append("WHERE project.client_id = client.client_id ");
            sb.Append("AND projectstatus.status_id = project.status_id ");
            sb.Append("AND projectstatus.is_visible = 1 ");
            sb.Append("ORDER BY client_name, project_name ");

            return data.GetTable(sb.ToString());
        }

        public double GetTotalProjectHoursByDate(
            long projectID, DateTime from, DateTime to)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM( charge_hours ) ");
            sb.Append("FROM chargeablehours ");
            sb.Append("WHERE charge_date >= '").Append(from.ToString("yyyy-MM-dd")).Append
                ("' ");
            sb.Append("AND charge_date <= '").Append(to.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("AND project_id = ").Append(projectID.ToString());
            try
            {
                return Convert.ToDouble(data.GetObject(sb.ToString()));
            }
            catch
            {
                return 0;
            }
        }

        public double GetTotalProjectHours(long projectID)
        {
            string query =
                "SELECT SUM( charge_hours ) " +
                "FROM chargeablehours " +
                "WHERE project_id = " + projectID;
            try
            {
                return Convert.ToDouble(data.GetObject(query));
            }
            catch // This is fugly
            {
                return 0;
            }
        }

        public DataTable GetAllProjectHours(long projectID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT charge_hours, charge_date, project_name, client_name, first_name, last_name, concat(last_name, ', ', first_name) AS full_name ");
            sb.Append("FROM chargeablehours, project, client, users ");
            sb.Append("WHERE chargeablehours.project_id = ").Append(projectID).Append(" ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND chargeablehours.project_id = project.project_id ");
            sb.Append("AND users.user_id = chargeablehours.user_id ");
            sb.Append("ORDER BY full_name, charge_date; ");
            return data.GetTable(sb.ToString());
        }

        public DataTable GetChargeableProjectHours(
            long projectID, DateTime from, DateTime to)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT charge_hours, charge_date, project_name, client_name, first_name, last_name, concat(last_name, ', ', first_name) AS full_name ");
            sb.Append("FROM chargeablehours, project, client, users ");
            sb.Append("WHERE chargeablehours.project_id = ").Append(projectID).Append(" ");
            sb.Append("AND charge_date >= '").Append(from.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("AND charge_date <= '").Append(from.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND chargeablehours.project_id = project.project_id ");
            sb.Append("AND users.user_id = chargeablehours.user_id ");
            sb.Append("ORDER BY full_name, charge_date; ");
            return data.GetTable(sb.ToString());
        }

        public DataTable GetProjectHoursForSummaryByDate(DateTime start, DateTime end)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT DISTINCT client_name, project_name, first_name, last_name, sum(charge_hours) AS total_hours ");
            sb.Append("FROM chargeablehours, project, users, client ");
            sb.Append("WHERE chargeablehours.project_id = project.project_id ");
            sb.Append("AND project.client_id = client.client_id ");
            sb.Append("AND chargeablehours.user_id = users.user_id ");
            sb.Append("AND charge_date >= '").Append(start.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("AND charge_date <= '").Append(end.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("GROUP BY project.project_id, users.user_id ");
            sb.Append("ORDER BY client_name, project_name, last_name, first_name; ");
            return data.GetTable(sb.ToString());
        }

        public DataTable GetProjectTotalHoursByDate(DateTime start, DateTime end)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT DISTINCT concat(client_name, ' - ', project_name) AS client_project, sum(charge_hours) AS total_hours ");
            sb.Append("FROM chargeablehours, project, client ");
            sb.Append("WHERE chargeablehours.project_id = project.project_id ");
            sb.Append("AND client.client_id = project.client_id ");
            sb.Append("AND charge_date >= '").Append(start.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("AND charge_date <= '").Append(end.ToString("yyyy-MM-dd")).Append(
                "' ");
            sb.Append("GROUP BY project.project_id ");
            sb.Append("ORDER BY client_project ");
            return data.GetTable(sb.ToString());
        }

        #endregion

        #region Activity

        public DataTable GetActivityHoursByDate(
            long activityID, DateTime from, DateTime to)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT non_charge_hours, non_charge_date, activity_name, concat(last_name, ', ', first_name) AS full_name ");
            sb.Append("FROM nonchargeablehours, activity, users ");
            sb.Append("WHERE nonchargeablehours.activity_id = ").Append(activityID).Append
                (" ");
            sb.Append("AND non_charge_date >= '").Append(from.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("AND non_charge_date <= '").Append(to.ToString("yyyy-MM-dd")).Append
                ("' ");
            sb.Append("AND nonchargeablehours.activity_id = activity.activity_id ");
            sb.Append("AND users.user_id = nonchargeablehours.user_id ");
            sb.Append("ORDER BY full_name, non_charge_date; ");
            return data.GetTable(sb.ToString());
        }

        public double GetTotalActivityHoursByDate(
            long activityID, DateTime from, DateTime to)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT SUM( non_charge_hours ) ");
            sb.Append("FROM nonchargeablehours ");
            sb.Append("WHERE non_charge_date >= '").Append(from.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("AND non_charge_date <= '").Append(to.ToString("yyyy-MM-dd")).Append
                ("' ");
            sb.Append("AND activity_id = ").Append(activityID.ToString());
            try
            {
                return Convert.ToDouble(data.GetObject(sb.ToString()));
            }
            catch
            {
                return 0;
            }
        }

        public DataTable GetActivityTotalHoursByDate(DateTime start, DateTime end)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "SELECT DISTINCT activity_name, sum(non_charge_hours) AS total_hours ");
            sb.Append("FROM nonchargeablehours, activity ");
            sb.Append("WHERE nonchargeablehours.activity_id = activity.activity_id ");
            sb.Append("AND non_charge_date >= '").Append(start.ToString("yyyy-MM-dd")).
                Append("' ");
            sb.Append("AND non_charge_date <= '").Append(end.ToString("yyyy-MM-dd")).
                Append
                ("' ");
            sb.Append("GROUP BY activity.activity_id ");
            sb.Append("ORDER BY activity_name ");
            return data.GetTable(sb.ToString());
        }

        #endregion
    } //class
} //namespace